package sql_old

import (
	"database/sql"
	"fmt"
)

func (c *conn) migrate() (int, error) {
	_, err := c.Exec(`
		create table if not exists migrations (
			num integer not null,
			at timestamptz not null
		);
	`)
	if err != nil {
		return 0, fmt.Errorf("creating migration table: %v", err)
	}

	i := 0
	done := false
	for {
		err := c.ExecTx(func(tx *trans) error {
			// Within a transaction, perform a single migration.
			var (
				num sql.NullInt64
				n   int
			)
			if err := tx.QueryRow(`select max(num) from migrations;`).Scan(&num); err != nil {
				return fmt.Errorf("select max migration: %v", err)
			}
			if num.Valid {
				n = int(num.Int64)
			}
			if n >= len(migrations) {
				done = true
				return nil
			}

			migrationNum := n + 1
			m := migrations[n]
			if _, err := tx.Exec(m.stmt); err != nil {
				return fmt.Errorf("migration %d failed: %v", migrationNum, err)
			}

			q := `insert into migrations (num, at) values ($1, now());`
			if _, err := tx.Exec(q, migrationNum); err != nil {
				return fmt.Errorf("update migration table: %v", err)
			}
			return nil
		})
		if err != nil {
			return i, err
		}
		if done {
			break
		}
		i++
	}

	return i, nil
}

type migration struct {
	stmt string
	// TODO(ericchiang): consider adding additional fields like "forDrivers"
}

// All SQL flavors share migration strategies.
var migrations = []migration{
	{
		stmt: `
			create table users_sub_id (
				sub text not null primary key,	-- holds the authenticated subject
				id text not null	-- Relation to users.id
			);

			create table users (
				id SERIAL primary key,
				subjects text [],
				name text,
 				givenname text,
				familyname text,
				email text,
				emailverified boolean,
				phonenumber text,
				groups text []
			);

			create table access_tokens (
				id text not null primary key,
				sub text not null,
				active boolean not null,
				scope text not null,
				clientid text not null,
				username text not null, 
				tokentype text not null,
 				expiration text not null,
				audience text not null
			);
		
			-- keys is a weird table because we only ever expect there to be a single row
			create table keys (
				id text not null primary key,
				verification_keys bytea not null, -- JSON array
				signing_key bytea not null,       -- JSON object
				signing_key_pub bytea not null,   -- JSON object
				next_rotation timestamptz not null
			);
			`,
	},
}
